﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Text;

    public class PagingHelper
    {
        public static string GetPagingSQL(string mainSql, string currentPage, string pageSize)
        {
            return GetPagingSQL(mainSql, currentPage, pageSize, null, null);
        }

        public static string GetPagingSQL(string mainSql, string currentPage, string pageSize, string sortField, string sortDirection)
        {
            if (!string.IsNullOrEmpty(sortField))
            {
                mainSql = mainSql + " ORDER BY " + sortField + " ";
                if (!string.IsNullOrEmpty(sortField))
                {
                    mainSql = mainSql + sortDirection;
                }
            }
            StringBuilder tempSql = new StringBuilder();
            tempSql.AppendLine("declare @currentpage int ");
            tempSql.AppendLine("declare @pagesize int ");
            int CS00002 = int.Parse(currentPage) + 1;
            tempSql.AppendLine(string.Format("set @currentpage = '{0}' ", CS00002.ToString()));
            tempSql.AppendLine(string.Format("set @pagesize = '{0}' ", pageSize));
            tempSql.AppendLine("set nocount on ");
            tempSql.AppendLine("declare @P1 int, @rowcount int ");
            tempSql.AppendFormat("exec sp_cursoropen @P1 output,'{0}',@scrollopt=1,@ccopt=1, @rowcount=@rowcount output ", mainSql.Replace("'", "''"));
            tempSql.AppendLine("select ceiling(1.0*@rowcount/@pagesize) as TotalPagesCount,@rowcount as TotalRecordsCount,@currentpage as CurrentPage ");
            tempSql.AppendLine("set @currentpage=(@currentpage-1)*@pagesize+1 ");
            tempSql.AppendLine("exec sp_cursorfetch @P1,16,@currentpage,@pagesize ");
            tempSql.AppendLine("exec sp_cursorclose @P1 ");
            tempSql.AppendLine("set nocount off ");
            return tempSql.ToString();
        }
    }
}

